#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Date: 2020/8/19 11:57
# @Author: 刘利 18603013656
# @File: company.py
# @Software: PyCharm

import sqlite3

def db_process():
    con = sqlite3.connect(r'..\spider_51job_WeakManager\company.db')
    cur = con.cursor()

    sql1 = 'select id, company from jobs'
    sql2 = 'select id, company from jobs group by company'

    data1 = cur.execute(sql1).fetchall()
    data2 = cur.execute(sql2).fetchall()
    print(len(data1))
    print(len(data2))

    cur.execute("select name from sqlite_master where type='table' order by name").fetchall()
    cur.fetchall()

    cur.execute("PRAGMA table_info(company_new)").fetchall()

    cur.execute('create table company_new (id integer primary key not null, com text not null, com_url)')
    cur.execute('insert into company_new select null, company from jobs')
    cur.execute('select * from Comp').fetchall()

    cur.execute('alter table company_new add company_url text')
    cur.execute('insert into company_new(company) select company_url from jobs')

    con.commit()
    cur.execute('create table table_com (id integer primary key not null, company text not null, company_url, email)')
    cur.execute('pragma table_info(table_com)').fetchall()

    cur.execute('create table com_new select * from jobs where 1=2')
    cur.execute('insert into company_new(com, com_url) select company, jobs.company_url from jobs')

    data = cur.execute('select * from company_new group by com').fetchall()
    len(data)
    data[1][1]
    sql1 = 'create table company_new'
    for i in range(len(data)):
        cur.execute('''
        insert into Comp(company, company_url) values (?, ?)''', (data[i][1], data[i][2]))

    cur.execute('''insert into Comp (company, company_url) values (?, ?)''', ('a', 'a_url'))
    cur.execute('select * from Comp').fetchall()
    cur.execute('delete from Comp where id=1')
    con.commit()